[進程。Processing] 12.塑型(Modeling)

DAY 12

進程。Processing系列第 12 篇

鐵人賽 processing

jamesjan

2012-10-12 08:48:50

3589 瀏覽

分享至

James 思考著，要讓每一個區塊的資料快速的顯示，直接存取 Table 是最快的作法了。
從開發 EIS 的經驗，一張報表對應一個 Table/View，讓資料彙整的工作在前一天 Batch 方式處理。而且採購簽核的特性是，在當下所得到的資訊，會隨著時間的移動而變化，如果日後要去追溯簽核當時的情況，直接存取現有的資料是會失真的。所以，在建構資料模型時，要將當下的資訊儲存起來。

這一段如果擺在使用者簽核時才做，那勢必會讓使用者耗費在等待的時間上，這樣使用者就不會買單了，所以事先將簽核所需參考的資訊整理出來，是首要之務。

James 在 Visio 上開始試做資料模型架構，流程在啟動後，通知簽核者簽核時，在頁面上可以調出相關的庫存分析與採購、銷售分析。(圖1)

圖1：資料模型架構

針對三大區塊的資料，James 定義了三支 Stored Procedure 來產生所需的資料。首先針對超額庫存的區塊進行 Data Model 的設計，在超額庫存中，主要是以 PM 的銷售預測，加上原廠 Backlog 的預計進貨量，對未來庫存的預估。

當月預估庫存=當月庫存+當月原廠 Backlog - (if 當月銷售>當月 PM 銷售預測 then 0 else 當月 PM 銷售預測 - 當月銷售)
次月預估庫存=當月預估庫存+次月原廠 Backlog - 次月 PM 銷售預測

當月計算公式不同的地方，在於已經銷售的量已經反應在庫存數字上，所以銷售預估是扣掉預計還要出貨的量。

這裡還要反映出 Slow 以及超額/短缺的變化，所以 James 針對當月的 Slow 計算，必須回推四個月的庫存數，以及三個月的銷售數，才可以得出目前的 Slow 狀況。而 PM 的銷售預測只做到未來四個月，所以依據超額庫存的定義：

超額庫存=當月庫存量>未來 2 個月的銷售預測

超額庫存只能計算到未來 2 個月。James 將定義釐清後，先設計 PO_stock_proc 的 Table Schema，並開始在 Stored Procedure 中撰寫資料分析的程式。

PO_stock_proc schema

CREATE TABLE [dbo].[PO_stock_proc] (
     [SLIP_NO] [char] (10) NOT NULL ,
     [SLIP_DATE] [char] (10) NOT NULL ,
     [part_id] [varchar] (20) NOT NULL ,
     [qty_5] [decimal](15, 0) NULL ,
     [qty_4] [decimal](15, 0) NULL ,
     [qty_3] [decimal](15, 0) NULL ,
     [qty_2] [decimal](15, 0) NULL ,
     [qty_1] [decimal](15, 0) NULL ,
     [qty] [decimal](15, 0) NULL ,
     [fq] [decimal](15, 3) NULL ,
     [fq_1] [decimal](15, 3) NULL ,
     [fq_2] [decimal](15, 3) NULL ,
     [fq_3] [decimal](15, 3) NULL ,
     [fq_4] [decimal](15, 3) NULL ,
     [fqs] [decimal](15, 3) NULL ,
     [fq_1s] [decimal](15, 3) NULL ,
     [fq_2s] [decimal](15, 3) NULL ,
     [fq_3s] [decimal](15, 3) NULL ,
     [fq_4s] [decimal](15, 3) NULL ,
     [pq_d] [decimal](15, 0) NULL ,
     [pq] [decimal](15, 0) NULL ,
     [pq_1] [decimal](15, 0) NULL ,
     [pq_2] [decimal](15, 0) NULL ,
     [pq_3] [decimal](15, 0) NULL ,
     [sq_4] [decimal](18, 0) NULL ,
     [sq_3] [decimal](18, 0) NULL ,
     [sq_2] [decimal](18, 0) NULL ,
     [sq_1] [decimal](18, 0) NULL ,
     [sq] [decimal](18, 0) NULL ,
     [eq] [decimal](15, 0) NULL ,
     [eq_1] [decimal](15, 0) NULL ,
     [eq_2] [decimal](15, 0) NULL ,
     [fsq] [decimal](15, 0) NULL ,
     [brand_nm] [varchar] (15) NULL ,
     [dep_id] [varchar] (10) NULL ,
     [dep_id_3] [varchar] (10) NULL ,
     [dep_id_4] [varchar] (10) NULL ,
     [st_4] [int] NULL ,
     [st_3] [int] NULL ,
     [st_2] [int] NULL ,
     [st_1] [int] NULL ,
     [st] [int] NULL ,
     [ft] [int] NULL ,
     [ft_1] [int] NULL ,
     [ft_2] [int] NULL ,
     [updated] [datetime] NULL ,
     [updater] [varchar] (10) NULL
) ON [PRIMARY]
GO

在 Stored Procedure 中針對 Schema 欄位宣告相對應的變數，並將各自的數據一一 Retrive 出來，並計算相關的週轉天數，再將結果寫入 PO_stock_proc 中。

sp_PO_stock_proc

CREATE PROCEDURE [dbo].[sp_PO_stock_proc]
@slip_no as char(10),
@slip_date as char(10),
@part_id as varchar(20)
AS
BEGIN

declare @d datetime

set @d=convert(datetime,@slip_date)

declare @qty_5 decimal(15,0)
declare @qty_4 decimal(15,0)
declare @qty_3 decimal(15,0)
declare @qty_2 decimal(15,0)
declare @qty_1 decimal(15,0)
declare @qty decimal(15,0)
declare @fq decimal(15,0)
declare @fq_1 decimal(15,0)
declare @fq_2 decimal(15,0)
declare @fq_3 decimal(15,0)
declare @fq_4 decimal(15,0)
declare @fqs decimal(15,0)
declare @fq_1s decimal(15,0)
declare @fq_2s decimal(15,0)
declare @fq_3s decimal(15,0)
declare @fq_4s decimal(15,0)
declare @pq_d decimal(15,0)
declare @pq decimal(15,0)
declare @pq_1 decimal(15,0)
declare @pq_2 decimal(15,0)
declare @pq_3 decimal(15,0)
declare @sq_4 decimal(15,0)
declare @sq_3 decimal(15,0)
declare @sq_2 decimal(15,0)
declare @sq_1 decimal(15,0)
declare @sq decimal(15,0)
declare @eq decimal(15,0)
declare @eq_1 decimal(15,0)
declare @eq_2 decimal(15,0)
declare @fsq decimal(15,0)
declare @brand_nm varchar(20)
declare @dep_id varchar(10)
declare @dep_id_3 varchar(10)
declare @dep_id_4 varchar(10)
declare @st_4 decimal(15,1)
declare @st_3 decimal(15,1)
declare @st_2 decimal(15,1)
declare @st_1 decimal(15,1)
declare @st decimal(15,1)
declare @ft decimal(15,1)
declare @ft_1 decimal(15,1)
declare @ft_2 decimal(15,1)

delete PO_stock_proc where SLIP_NO=@slip_no and SLIP_DATE=@slip_date and part_id=@part_id

-- 前6個月庫存(含當月)
set @qty_5=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), DATEADD(m, - 5, @d), 111))),0)
set @qty_4=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), DATEADD(m, - 4, @d), 111))),0)
set @qty_3=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), DATEADD(m, - 3, @d), 111))),0)
set @qty_2=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), DATEADD(m, - 2, @d), 111))),0)
set @qty_1=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), DATEADD(m, - 1, @d), 111))),0)
set @qty=isnull((select dbo.getStock_yymm(@part_id,CONVERT(char(7), @d, 111))),0)

-- PM Forecast
set @fq=isnull((select dbo.getPMFcst(CONVERT(char(7), @d, 111), @part_id)),0)
set @fq_1=isnull((select dbo.getPMFcst(CONVERT(char(7), DATEADD(m, 1, @d), 111), @part_id)),0)
set @fq_2=isnull((select dbo.getPMFcst(CONVERT(char(7), DATEADD(m, 2, @d), 111), @part_id)),0)
set @fq_3=isnull((select dbo.getPMFcst(CONVERT(char(7), DATEADD(m, 3, @d), 111), @part_id)),0)
set @fq_4=isnull((select dbo.getPMFcst(CONVERT(char(7), DATEADD(m, 4, @d), 111), @part_id)),0)
set @fqs=isnull((select dbo.getSalesFcst(CONVERT(char(7), @d, 111), @part_id)),0)

-- Sales Forecast，For 超額庫存計算
set @fq_1s=isnull((select dbo.getSalesFcst(CONVERT(char(7), DATEADD(m, 1, @d), 111), @part_id)),0)
set @fq_2s=isnull((select dbo.getSalesFcst(CONVERT(char(7), DATEADD(m, 2, @d), 111), @part_id)),0)
set @fq_3s=isnull((select dbo.getSalesFcst(CONVERT(char(7), DATEADD(m, 3, @d), 111), @part_id)),0)
set @fq_4s=isnull((select dbo.getSalesFcst(CONVERT(char(7), DATEADD(m, 4, @d), 111), @part_id)),0)

-- 原廠 Backlog
set @pq_d=isnull((select dbo.getPO_proc_k(@part_id, CONVERT(char(10), @d, 111), CONVERT(char(7), @d, 111), 'D',@slip_no)),0)
set @pq=isnull((select dbo.getPO_proc_k(@part_id, CONVERT(char(10), @d, 111), CONVERT(char(7), @d, 111), 'T',@slip_no)),0)
set @pq_1=isnull((select dbo.getPO_proc_k(@part_id, CONVERT(char(10), @d, 111),CONVERT(char(7), DATEADD(m, 1, @d), 111), '',@slip_no)),0)
set @pq_2=isnull((select dbo.getPO_proc_k(@part_id, CONVERT(char(10), @d, 111),CONVERT(char(7), DATEADD(m, 2, @d), 111), '',@slip_no)),0)
set @pq_3=isnull((select dbo.getPO_proc_k(@part_id, CONVERT(char(10), @d, 111),CONVERT(char(7), DATEADD(m, 2, @d), 111), 'A',@slip_no)),0)

-- 實際銷售
set @sq_4=isnull((SELECT SUM(isnull(qty, 0)) FROM sold_a with (nolock) WHERE part_id = @part_id and yymm=CONVERT(char(7), DATEADD(m, -4, @d), 111)),0)
set @sq_3=isnull((SELECT SUM(isnull(qty, 0)) FROM sold_a with (nolock) WHERE part_id = @part_id and yymm=CONVERT(char(7), DATEADD(m, -3, @d), 111)),0)
set @sq_2=isnull((SELECT SUM(isnull(qty, 0)) FROM sold_a with (nolock) WHERE part_id = @part_id and yymm=CONVERT(char(7), DATEADD(m, -2, @d), 111)),0)
set @sq_1=isnull((SELECT SUM(isnull(qty, 0)) FROM sold_a with (nolock) WHERE part_id = @part_id and yymm=CONVERT(char(7), DATEADD(m, -1, @d), 111)),0)
set @sq=isnull((SELECT SUM(isnull(qty, 0)) FROM sold_a with (nolock) WHERE part_id = @part_id and yymm=CONVERT(char(7), @d, 111)),0)

-- Slow Moving 預設值
set @st_4=999     -- 前 4 月
set @st_3=999     -- 前 3 月
set @st_2=999     -- 前 2 月
set @st_1=999     -- 前 1 月
set @st=999     -- 當月
set @ft=999     -- 預估當月
set @ft_1=999     -- 次月
set @ft_2=999     -- 次 2 月

-- 預估當月剩餘銷售：if 實際銷售>PM預估銷售 then 0 else PM預估銷售-實際銷售
set @fsq=case when @sq>@fq then 0 else @fq-@sq end

-- 預估當月庫存
set @eq=case when MONTH(@d)<MONTH(getdate()) then @qty else @qty+@pq_d+@pq-@fsq end
-- 預估次月庫存
set @eq_1=@eq+@pq_1-@fq_1
-- 預估次 2 月庫存
set @eq_2=@eq_1+@pq_2-@fq_2

select @brand_nm=brand_nm,@dep_id=dep_id,@dep_id_3=dep_id_3,@dep_id_4=dep_id_4 from ima_brand_dept where part_id=@part_id

-- 週轉天數計算
-- if 銷售>0 then
--   (上月期末庫存數+當月期末庫存數)/2*30天/當月銷售
-- else if (上月期末庫存數+當月期末庫存數)/2<0
--   0
if @sq_4>0
begin
set @st_4=(@qty_5 + @qty_4) / 2 * 30 / @sq_4
end
else if (@qty_5 + @qty_4) / 2<=0
begin
set @st_4=0
end

if @sq_3>0
begin
set @st_3=(@qty_4 + @qty_3) / 2 * 30 / @sq_3
end
else if (@qty_4 + @qty_3) / 2<=0
begin
set @st_3=0
end

if @sq_2>0
begin
set @st_2=(@qty_3 + @qty_2) / 2 * 30 / @sq_2
end
else if (@qty_3 + @qty_2) / 2<=0
begin
set @st_2=0
end

if @sq_1>0
begin
set @st_1=(@qty_2 + @qty_1) / 2 * 30 / @sq_1
end
else if (@qty_2 + @qty_1) / 2<=0
begin
set @st_1=0
end

-- 當月週轉天數，參數值天數以實際天數計算
if @sq>0
begin
set @st=(@qty_1 + @qty) / 2 * DAY(@d) / @sq
end
else if (@qty_1 + @qty) / 2<=0
begin
set @st=0
end

-- 預估當月庫存，if 當月銷售>PM預估銷售 且 >0
if @sq>@fq and @sq>0
begin
set @ft=(@qty_1 + @eq) / 2 * 30 / @sq
end
-- if PM預估銷售>0
else if @fq>0
begin
set @ft=(@qty_1 + @eq) / 2 * 30 / @fq
end
-- if 平均庫存<=0
else if (@qty_1 + @eq) / 2<=0
begin
set @ft=0
end

if @fq_1>0
begin
set @ft_1=(@eq + @eq_1) / 2 * 30 / @fq_1
end
else if (@eq + @eq_1) / 2<=0
begin
set @ft_1=0
end

if @fq_2>0
begin
set @ft_2=(@eq_1 + @eq_2) / 2 * 30 / @fq_2
end
else if (@eq_1 + @eq_2) / 2<=0
begin
set @ft_2=0
end

insert into PO_stock_proc
SELECT @slip_no as SLIP_NO,@slip_date as SLIP_DATE,@part_id as part_id,
isnull(@qty_5,0) as qty_5,isnull(@qty_4,0) as qty_4,isnull(@qty_3,0) as qty_3,isnull(@qty_2,0) as qty_2,isnull(@qty_1,0) as qty_1,isnull(@qty,0) as qty,
isnull(@fq,0) as fq,isnull(@fq_1,0) as fq_1,isnull(@fq_2,0) as fq_2,isnull(@fq_3,0) as fq_3,isnull(@fq_4,0) as fq_4,
isnull(@fqs,0) as fqs,isnull(@fq_1s,0) as fq_1s,isnull(@fq_2s,0) as fq_2s,isnull(@fq_3s,0) as fq_3s,isnull(@fq_4s,0) as fq_4s,
isnull(@pq_d,0) as pq_d,isnull(@pq,0) as pq,isnull(@pq_1,0) as pq_1,isnull(@pq_2,0) as pq_2,isnull(@pq_3,0) as pq_3,
isnull(@sq_4,0) as sq_4,isnull(@sq_3,0) as sq_3,isnull(@sq_2,0) as sq_2,isnull(@sq_1,0) as sq_1,isnull(@sq,0) as sq,isnull(@eq,0) as eq,isnull(@eq_1,0) as eq_1,isnull(@eq_2,0) as eq_2,isnull(@fsq,0) as fsq,
@brand_nm as brand_nm,@dep_id as dep_id,@dep_id_3 as dep_id_3,@dep_id_4 as dep_id_4,
isnull(@st_4,0) as st_4,isnull(@st_3,0) as st_3,isnull(@st_2,0) as st_2,isnull(@st_1,0) as st_1,isnull(@st,0) as st,isnull(@ft,0) as ft,isnull(@ft_1,0) as ft_1,isnull(@ft_2,0) as ft_2,getdate()

END
GO

這樣在啟動流程的同時，就先將超額庫存分析資訊先產生好，採購分析網頁只要存取相對應的 Table，這樣可以加快顯示的速度。James 將各個所需的資訊透過這種模式產生好，之後有需要調整，只需要修改 Data Model 即可，可減少網頁修改的 Effort，同時也可以在 Server 端檢核資料的正確性。

Prev Next

本篇全系列文章